package com.hnl.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Collection;
import java.util.HashMap;
import java.util.Map;
import java.util.Vector;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.hnl.core.connection.ConnectionManager;
import com.hnl.core.connection.ConnectionManagerFactory;
import com.hnl.to.Misc;
import com.hnl.util.FormatUtil;

public class DBMiscDAO implements MiscDAO {
	public static final Logger LOG = LoggerFactory.getLogger(DBMiscDAO.class);

	private PreparedStatement saveStmt;
	private PreparedStatement loadStmt;
	private PreparedStatement loadAllStmt;
	private PreparedStatement updateStmt;

	protected DBMiscDAO() throws SQLException {
		// Empty protected constructor to avoid unnecessary initialization
	}

	public Misc saveMisc(Misc misc) {
		ConnectionManager dbConnMgr = ConnectionManagerFactory
				.getConnectionManager(ConnectionManagerFactory.HNL);
		Connection conn = dbConnMgr.getConnection();

		try {
			saveStmt = conn
					.prepareStatement(
							"INSERT INTO misc (misc_type_ID, misc_name, misc_desc, create_date, create_user, modify_date, modify_user) "
									+ "VALUES (?,?,?,?,?,?,?)",
							PreparedStatement.RETURN_GENERATED_KEYS);
			saveStmt.setInt(1, misc.getMiscTypeID());
			saveStmt.setString(2, misc.getMiscName());
			saveStmt.setString(3, misc.getMiscDesc());
			saveStmt.setString(4, FormatUtil.getXmlDate(misc.getCreateDate()));
			saveStmt.setString(5, misc.getCreateUser().toString());
			saveStmt.setString(6, FormatUtil.getXmlDate(misc.getModifyDate()));
			saveStmt.setInt(7, misc.getModifyUser());
			saveStmt.executeUpdate();

			ResultSet rs = saveStmt.getGeneratedKeys();
			if (rs.next()) {
				misc.setMiscID(rs.getInt(1));
			}
			rs.close();
			saveStmt.close();
		} catch (SQLException e) {
			LOG.error("Error saving Misc.", e);
		} finally {
			dbConnMgr.releaseConnection(conn);
		}
		return misc;
	}

	public Misc loadMisc(Integer miscID) {
		ConnectionManager dbConnMgr = ConnectionManagerFactory
				.getConnectionManager(ConnectionManagerFactory.HNL);
		Connection conn = dbConnMgr.getConnection();

		Misc misc = null;
		try {
			loadStmt = conn
					.prepareStatement("SELECT misc_ID, misc_type_ID, misc_name, misc_desc, create_date, create_user, modify_date, modify_user FROM misc "
							+ "WHERE misc_ID = ?");
			loadStmt.setInt(1, miscID);
			ResultSet rs = loadStmt.executeQuery();
			if (rs.next()) {
				misc = new Misc();
				misc.setMiscID(rs.getInt("misc_ID"));
				misc.setMiscTypeID(rs.getInt("misc_type_ID"));
				misc.setMiscName(rs.getString("misc_name"));
				misc.setMiscName(rs.getString("misc_desc"));
				misc.setCreateDate(rs.getDate("create_date"));
				misc.setCreateUser(rs.getInt("create_user"));
				misc.setModifyDate(rs.getDate("modify_date"));
				misc.setModifyUser(rs.getInt("modify_user"));
			}
			rs.close();
			loadStmt.close();
		} catch (SQLException e) {
			LOG.error("Error load Misc.", e);
		} finally {
			dbConnMgr.releaseConnection(conn);
		}
		return misc;
	}

	public Misc loadMiscByMiscNameAndType(String miscName, String miscTypeName) {
		ConnectionManager dbConnMgr = ConnectionManagerFactory
				.getConnectionManager(ConnectionManagerFactory.HNL);
		Connection conn = dbConnMgr.getConnection();

		Misc misc = null;
		try {
			loadStmt = conn
					.prepareStatement("SELECT misc.misc_ID, misc.misc_type_ID, misc.misc_name, misc.misc_desc, misc.create_date, misc.create_user, misc.modify_date, misc.modify_user FROM misc, misc_type "
							+ "WHERE misc_name = ? AND type_name = ? AND misc.misc_type_ID = misc_type.misc_type_ID");
			loadStmt.setString(1, miscName);
			loadStmt.setString(2, miscTypeName);

			ResultSet rs = loadStmt.executeQuery();
			if (rs.next()) {
				misc = new Misc();
				misc.setMiscID(rs.getInt("misc_ID"));
				misc.setMiscTypeID(rs.getInt("misc_type_ID"));
				misc.setMiscName(rs.getString("misc_name"));
				misc.setMiscName(rs.getString("misc_desc"));
				misc.setCreateDate(rs.getDate("create_date"));
				misc.setCreateUser(rs.getInt("create_user"));
				misc.setModifyDate(rs.getDate("modify_date"));
				misc.setModifyUser(rs.getInt("modify_user"));
			}
			rs.close();
			loadStmt.close();
		} catch (SQLException e) {
			LOG.error("Error load Misc.", e);
		} finally {
			dbConnMgr.releaseConnection(conn);
		}
		return misc;
	}

	public Collection<Misc> loadMiscByMiscTypeName(String miscTypeName) {
		ConnectionManager dbConnMgr = ConnectionManagerFactory
				.getConnectionManager(ConnectionManagerFactory.HNL);
		Connection conn = dbConnMgr.getConnection();

		Collection<Misc> miscCollection = new Vector<Misc>();
		try {
			loadAllStmt = conn
					.prepareStatement("SELECT misc.misc_ID, misc.misc_type_ID, misc.misc_name, misc.misc_desc, misc.create_date, misc.create_user, misc.modify_date, misc.modify_user FROM misc, misc_type "
							+ "WHERE misc.misc_type_ID = misc_type.misc_type_ID AND misc_type.type_name = ?");
			loadAllStmt.setString(1, miscTypeName.toString());
			ResultSet rs = loadAllStmt.executeQuery();
			while (rs.next()) {
				Misc misc = new Misc();
				misc.setMiscID(rs.getInt("misc_ID"));
				misc.setMiscTypeID(rs.getInt("misc_type_ID"));
				misc.setMiscName(rs.getString("misc_name"));
				misc.setMiscName(rs.getString("misc_desc"));
				misc.setCreateDate(rs.getDate("create_date"));
				misc.setCreateUser(rs.getInt("create_user"));
				misc.setModifyDate(rs.getDate("modify_date"));
				misc.setModifyUser(rs.getInt("modify_user"));
				miscCollection.add(misc);
			}
			rs.close();
			loadAllStmt.close();
		} catch (SQLException e) {
			LOG.error("Error load Misc type by misc type and name.", e);
		} finally {
			dbConnMgr.releaseConnection(conn);
		}
		return miscCollection;
	}

	public void updateMisc(Misc misc) {
		ConnectionManager dbConnMgr = ConnectionManagerFactory
				.getConnectionManager(ConnectionManagerFactory.HNL);
		Connection conn = dbConnMgr.getConnection();

		try {
			updateStmt = conn
					.prepareStatement("UPDATE misc SET misc_type_ID=?, misc_name=?, misc_desc=?, create_date=?, create_user=?, modify_date=?, modify_user=? "
							+ "WHERE misc_ID=?");
			updateStmt.setInt(1, misc.getMiscTypeID());
			updateStmt.setString(2, misc.getMiscName());
			updateStmt.setString(3, misc.getMiscDesc());
			updateStmt
					.setString(4, FormatUtil.getXmlDate(misc.getCreateDate()));
			updateStmt.setString(5, misc.getCreateUser().toString());
			updateStmt
					.setString(6, FormatUtil.getXmlDate(misc.getModifyDate()));
			updateStmt.setInt(7, misc.getModifyUser());
			updateStmt.setInt(8, misc.getMiscID());
			updateStmt.executeUpdate();
			updateStmt.close();
		} catch (SQLException e) {
			LOG.error("Error update Misc.", e);
		} finally {
			dbConnMgr.releaseConnection(conn);
		}
	}

	public Map<Integer, String> loadMiscNameByMiscTypeName(String miscTypeName) {
		Map<Integer, String> miscNameMap = new HashMap<Integer, String>();

		Collection<Misc> miscVector = loadMiscByMiscTypeName(miscTypeName);
		for (Misc misc : miscVector) {
			miscNameMap.put(misc.getMiscID(), misc.getMiscName());
		}

		return miscNameMap;
	}

}
